﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Globalization;

public partial class Employees : System.Web.UI.Page
{

    SqlConnection con = new SqlConnection();
    DataView dv;
    DataTable dt;
   
    protected void Page_Load(object sender, EventArgs e)
    {

        con.ConnectionString = @"server=localhost;database=TSQLFundamentals2008;uid=sa;pwd=123456";

        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = constr;


        if (this.IsPostBack == false)
        {
            FillEmployees();
            LoadToC();
            LoadMgrId();
            LoadTitle();
            LoadFilterTitle();
        }

        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;
        btnAdd.Enabled = true;
        }
       void FillEmployees()
    {

        SqlDataAdapter da = new SqlDataAdapter("SELECT empid,lastname,firstname,title,titleofcourtesy,CONVERT(Varchar(50), birthdate,101) as birthdate,CONVERT(Varchar(50), hiredate,101) as hiredate,address,city,region,postalcode,country,phone,mgrid FROM HR.Employees", con);
        dt = new DataTable("HR.Employees");
        da.Fill(dt);
        gvEmployee.AutoGenerateSelectButton = true;
        gvEmployee.AllowPaging = true;
        gvEmployee.PageSize = 5;
        gvEmployee.DataSource = dt;
        gvEmployee.DataBind();
    }

    void LoadToC()
    {
       
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT titleofcourtesy FROM HR.Employees", con);
        DataTable dt = new DataTable("HR.Employees");
        da.Fill(dt);
        dllToC.DataSource = dt;
        dllToC.DataTextField = "titleofcourtesy";//cot hien ra trong giao dien
        dllToC.DataValueField = "titleofcourtesy";
        dllToC.DataBind();

    }
    void LoadTitle()
    {
 
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT title FROM HR.Employees", con);
        DataTable dt = new DataTable("HR.Employees");
        da.Fill(dt);
        dllTitle.DataSource = dt;
        dllTitle.DataTextField = "title";//cot hien ra trong giao dien
        dllTitle.DataValueField = "title";
        dllTitle.DataBind();
    }
    void LoadMgrId()
    {

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter("SELECT CAST(empid AS nvarchar)+' '+firstname+' '+lastname AS fullname, empid FROM HR.Employees", con);
        DataTable dt = new DataTable("HR.Employees");
        da.Fill(dt);
        dllMgrId.DataSource = dt;
        dllMgrId.DataTextField = "fullname";//cot hien ra trong giao dien
        dllMgrId.DataValueField = "empid";
        dllMgrId.DataBind();
    }
    void LoadFilterTitle()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT title FROM HR.Employees", con);
        DataTable dt = new DataTable("HR.Employees");
        da.Fill(dt);
        dllFilterTitle.DataSource = dt;
        dllFilterTitle.DataTextField = "title";//cot hien ra trong giao dien
        dllFilterTitle.DataValueField = "title";
        dllFilterTitle.DataBind();
    }
    void addEmployee()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "LoadEmployeeTable";

            SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar, 20);
            param.Value = txtLastname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@firstname", SqlDbType.NVarChar, 10);
            param.Value = txtFirstname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@title", SqlDbType.NVarChar, 30);
            param.Value = dllTitle.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar, 25);
            param.Value = dllToC.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@birthdate", SqlDbType.DateTime);
            //param.Value = DateTime.ParseExact(txtBirthday.Text,("dd/MM/yyyy"),CultureInfo.InvariantCulture);
            param.Value = DateTime.Parse(txtBirthday.Text).ToShortDateString() ;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@hiredate", SqlDbType.DateTime);
           // param.Value = DateTime.ParseExact(txtHireday.Text, ("dd/MM/yyyy"), CultureInfo.InvariantCulture);
            param.Value = DateTime.Parse(txtHireday.Text).ToShortDateString() ;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
            param.Value = txtAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
            param.Value = txtCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
            if (txtRegion.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
            if (txtPostalcode.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtPostalcode.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
            param.Value = dllCountry.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
            param.Value = txtPhone.Text;
            cmd.Parameters.Add(param);

            if (cbMgrId.Checked == true)
            {
                param = new SqlParameter("@mgrid", SqlDbType.Int);
                param.Value = int.Parse(dllMgrId.Text);
                cmd.Parameters.Add(param);
            }
            else
            {
                param = new SqlParameter("@mgrid", SqlDbType.Int);
                param.Value = System.Convert.DBNull;
                cmd.Parameters.Add(param);
            }

            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('ADD SUCCESSFULLY'); </script>");
        
            }
            catch (Exception ex)
            {
                Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT ADD THIS ROW'); </script>");
            }
            finally
            {
            con.Close();
            FillEmployees();
        }
    }
    void updateEmployee()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UpdateEmployeeTable";

            SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar, 20);
            param.Value = txtLastname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empId", SqlDbType.Int);
            param.Value = int.Parse(txtID.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@firstname", SqlDbType.NVarChar, 10);
            param.Value = txtFirstname.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@title", SqlDbType.NVarChar, 30);
            param.Value = dllTitle.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar, 25);
            param.Value = dllToC.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@birthdate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtBirthday.Text).ToShortDateString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@hiredate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtHireday.Text).ToShortDateString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
            param.Value = txtAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
            param.Value = txtCity.Text;
            cmd.Parameters.Add(param);

            if (cbMgrId.Checked == true)
            {
                param = new SqlParameter("@mgrid", SqlDbType.Int);
                param.Value = int.Parse(dllMgrId.Text);
                cmd.Parameters.Add(param);
            }
            else
            {
                param = new SqlParameter("@mgrid", SqlDbType.Int);
                param.Value = System.Convert.DBNull;
                cmd.Parameters.Add(param);
            }

            param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
            if (txtRegion.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
            if (txtPostalcode.Text.Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtPostalcode.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
            param.Value = dllCountry.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
            param.Value = txtPhone.Text;
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('UPDATE THIS ROW SUCCESSFULLY'); </script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT UPDATE THIS ROW'); </script>");
        }
        finally
        {
            con.Close();
            FillEmployees();
       }
    }
    void deleteEmployee()
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "deleteEmployee";
            SqlParameter param = new SqlParameter("@empId", SqlDbType.Int);
            param.Value = int.Parse(txtID.Text);
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('DELETE THIS ROW SUCCESSFULLY'); </script>");
        
        }
        catch (Exception ex)
        {
            Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT DELETE THIS ROW'); </script>");
        }
        finally
        {
            FillEmployees();
            con.Close();
        }
    }

    protected void gvEmployee_SelectedIndexChanged(object sender, EventArgs e)
    {
                 btnUpdate.Enabled = true;
                 btnDelete.Enabled = true;
                 btnAdd.Enabled = false;
                 try
                 {

            int index = gvEmployee.SelectedIndex;
            Session["index_Employee"] = index;

            GridViewRow r = gvEmployee.SelectedRow;
            txtID.Text = r.Cells[1].Text;
            txtLastname.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtFirstname.Text = Server.HtmlDecode(r.Cells[3].Text);
            dllTitle.SelectedItem.Text = Server.HtmlDecode(r.Cells[4].Text);
            dllToC.SelectedItem.Text = Server.HtmlDecode(r.Cells[5].Text);
            txtBirthday.Text = Server.HtmlDecode(r.Cells[6].Text);
            txtHireday.Text = Server.HtmlDecode(r.Cells[7].Text);
            txtAddress.Text = Server.HtmlDecode(r.Cells[8].Text);
            txtCity.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtRegion.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtPostalcode.Text = Server.HtmlDecode(r.Cells[11].Text);
            dllCountry.SelectedItem.Text = Server.HtmlDecode(r.Cells[12].Text);
            txtPhone.Text = Server.HtmlDecode(r.Cells[13].Text);

            if (Server.HtmlDecode(r.Cells[14].Text).Trim().Length == 0)
            {
                cbMgrId.Checked = false;
            }
            else
            {
                dllMgrId.SelectedValue = Server.HtmlDecode(r.Cells[14].Text);
                cbMgrId.Checked = true;
                
            }

        }catch(Exception ex)
        {
            Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT CHOOSE THIS ROW'); </script>");
        }
    }

    protected void gvEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        FillEmployees();
        if ((bool)Session["Employee_Search"] == false)
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            gvEmployee.DataBind();
        }
        else
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("lastname like '{0}%'", txtSearch.Text);
            gvEmployee.DataSource = dv;
            gvEmployee.DataBind();
        }
        if ((bool)Session["Employee_Search1"] == false)
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            gvEmployee.DataBind();
        }
        else
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("firstname like '{0}%'", txtSearch.Text);
            gvEmployee.DataSource = dv;
            gvEmployee.DataBind();
        }

        if ((bool)Session["Employee_Search2"] == false)
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            gvEmployee.DataBind();
        }
        else
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("title like '{0}%'", dllFilterTitle.Text);
            gvEmployee.DataSource = dv;
            gvEmployee.DataBind();
        }
        if ((bool)Session["Employee_Search3"] == false)
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            gvEmployee.DataBind();
        }
        else
        {
            gvEmployee.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("country like '{0}%'", dllFiltercountry.Text);
            gvEmployee.DataSource = dv;
            gvEmployee.DataBind();
        }

    }

    void SearchByLastName()
    {
        FillEmployees();
        Session["Employee_Search"] = true;
        dv = new DataView(dt);
        dv.RowFilter = string.Format("lastname like '{0}%'", txtSearch.Text);
        gvEmployee.DataSource = dv;
        gvEmployee.DataBind();
        Session["index_Employee"] = -1;
    }

    void SearchByFirstName()
    {
        FillEmployees();
        Session["Employee_Search1"] = true;
        dv = new DataView(dt);
        dv.RowFilter = string.Format("firstname like '{0}%'", txtSearch.Text);
        gvEmployee.DataSource = dv;
        gvEmployee.DataBind();
        Session["index_Employee"] = -1;
    }

    void FilterTitle()
    {
        FillEmployees();
        Session["Employee_Search2"] = true;
        dv = new DataView(dt);
        dv.RowFilter = string.Format("title like '{0}%'", dllFilterTitle.Text);
        gvEmployee.DataSource = dv;
        gvEmployee.DataBind();
        Session["index_Employee"] = -1;
    }

    void FilterCountry()
    {
        FillEmployees();
        Session["Employee_Search3"] = true;
        dv = new DataView(dt);
        dv.RowFilter = string.Format("country like '{0}%'", dllFiltercountry.Text);
        gvEmployee.DataSource = dv;
        gvEmployee.DataBind();
        Session["index_Employee"] = -1;
    }

    protected void btsearchLastname_Click(object sender, EventArgs e)
    {
        SearchByLastName();
    }

    protected void btSearchfirstName_Click(object sender, EventArgs e)
    {
        SearchByFirstName();
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        addEmployee();
        Session["index_Employee"] = -1;
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        updateEmployee();
        LoadToC();
        LoadMgrId();
        LoadTitle();
        LoadFilterTitle();
        Session["index_Employee"] = -1;
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteEmployee();
        Session["index_Employee"] = -1;
    }

    protected void btFilterTitle_Click(object sender, EventArgs e)
    {
        FilterTitle();
        Session["index_Employee"] = -1;
    }


   

    protected void btnNew_Click(object sender, EventArgs e)
    {
        FillEmployees();
        LoadToC();
        LoadMgrId();
        LoadTitle();
        LoadFilterTitle();
        txtID.Text = "";
        txtAddress.Text = "";
        txtBirthday.Text = "";
        txtHireday.Text = "";
        txtLastname.Text = "";
        txtRegion.Text = "";
        txtFirstname.Text = "";
        txtRegion.Text = "";
        txtPostalcode.Text = "";
        txtPhone.Text = "";
        txtCity.Text = "";
        Session["Employee_Search"] = false;
        Session["Employee_Search1"] = false;
        Session["Employee_Search2"] = false;
        Session["Employee_Search3"] = false;

        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;
        btnAdd.Enabled = true;

    }

    protected void btFilterCountry_Click(object sender, EventArgs e)
    {
        FilterCountry();
        Session["index_Employee"] = -1;
    }

   

}
